﻿

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using MySql.Data.MySqlClient;

public class DAL_siconide_baking
{
    public DAL_siconide_baking()
    {

    }



    public List<siconide_baking> select(int ID)
    {
        List<siconide_baking> list = new List<siconide_baking>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select ID,Baking_Date,Start_Time,End_Time,Drying_temp,Operator,Remark,IS_ACTIVE,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE from siconide_baking where ID=@ID";
        cmd.Parameters.AddWithValue("@ID", ID);
        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new siconide_baking()
            {
                ID = Convert.ToInt32(dr["ID"]),
                Baking_Date = dr["Baking_Date"].toDateTimeWithoutError(),
                Start_Time = Convert.ToDateTime("01/01/1900 " + dr["Start_Time"].ToString()),
                End_Time = Convert.ToDateTime("01/01/1900 " + dr["End_Time"].ToString()),
                Drying_temp = Convert.ToDecimal(dr["Drying_temp"]),
                Operator = dr["Operator"].ToString(),
                Remark = dr["Remark"].ToString(),
                IS_ACTIVE = Convert.ToBoolean(dr["IS_ACTIVE"]),
                CREATED_BY = dr["CREATED_BY"].ToString(),
                CREATED_DATE = dr["CREATED_DATE"].toDateTimeWithoutError(),
                UPDATED_BY = dr["UPDATED_BY"].ToString(),
                UPDATED_DATE = dr["UPDATED_DATE"].toDateTimeWithoutError()
            });
        }

        return list;
    }



    public List<siconide_baking> selectAll()
    {
        List<siconide_baking> list = new List<siconide_baking>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select ID,Baking_Date,Start_Time,End_Time,Drying_temp,Operator,Remark,IS_ACTIVE,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE from siconide_baking Order By ID";

        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new siconide_baking()
            {
                ID = Convert.ToInt32(dr["ID"]),
                Baking_Date = dr["Baking_Date"].toDateTimeWithoutError(),
                Start_Time = Convert.ToDateTime("01/01/1900 " + dr["Start_Time"].ToString()),
                End_Time = Convert.ToDateTime("01/01/1900 " + dr["End_Time"].ToString()),
                Drying_temp = Convert.ToDecimal(dr["Drying_temp"]),
                Operator = dr["Operator"].ToString(),
                Remark = dr["Remark"].ToString(),
                IS_ACTIVE = Convert.ToBoolean(dr["IS_ACTIVE"]),
                CREATED_BY = dr["CREATED_BY"].ToString(),
                CREATED_DATE = dr["CREATED_DATE"].toDateTimeWithoutError(),
                UPDATED_BY = dr["UPDATED_BY"].ToString(),
                UPDATED_DATE = dr["UPDATED_DATE"].toDateTimeWithoutError()
            });
        }

        return list;
    }

    public DataTable selectAllForGrid()
    {
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = @"Select ID,sb.Baking_Date as 'Baking_Date',sb.Start_Time,sb.End_Time,sb.Drying_temp,em.FIRST_NAME as Operator,sb.Remark,CASE WHEN sb.IS_ACTIVE =1 THEN 'True' ELSE 'False' END as 'IS_ACTIVE',sb.CREATED_BY,sb.CREATED_DATE as CREATED_DATE,sb.UPDATED_BY,sb.UPDATED_DATE
                from siconide_baking sb LEFT JOIN tbl_employee_master em on sb.Operator=em.USER_CODE Order By sb.ID desc";

        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        return dt;
    }


    public int insert(DateTime Baking_Date, DateTime Start_Time, DateTime End_Time, Decimal Drying_temp, String Operator, String Remark, String CREATED_BY, DateTime CREATED_DATE)
    {
        int i = 0;
        string sql = @"Insert into siconide_baking(Baking_Date, Start_Time, End_Time, Drying_temp, Operator, Remark, CREATED_BY, CREATED_DATE)
                        values(@Baking_Date, @Start_Time, @End_Time, @Drying_temp, @Operator, @Remark, @CREATED_BY, @CREATED_DATE)";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@Baking_Date", Baking_Date);
        cmd.Parameters.AddWithValue("@Start_Time", Start_Time.ToString("yyyy-MM-dd HH:mm:ss"));
        cmd.Parameters.AddWithValue("@End_Time", End_Time.ToString("yyyy-MM-dd HH:mm:ss"));
        cmd.Parameters.AddWithValue("@Drying_temp", Drying_temp);
        cmd.Parameters.AddWithValue("@Operator", Operator);
        cmd.Parameters.AddWithValue("@Remark", Remark);
        cmd.Parameters.AddWithValue("@CREATED_BY", CREATED_BY);
        cmd.Parameters.AddWithValue("@CREATED_DATE", CREATED_DATE);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }



    public int update(int ID, DateTime Baking_Date, DateTime Start_Time, DateTime End_Time, Decimal Drying_temp, String Operator, String Remark, String UPDATED_BY, DateTime UPDATED_DATE)
    {
        int i = 0;
        string sql = @"Update siconide_baking SET Baking_Date=@Baking_Date, Start_Time=@Start_Time, End_Time=@End_Time, Drying_temp=@Drying_temp, Operator=@Operator, Remark=@Remark, UPDATED_BY=@UPDATED_BY, UPDATED_DATE=@UPDATED_DATE WHERE ID=@ID";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@ID", ID);
        cmd.Parameters.AddWithValue("@Baking_Date", Baking_Date);
        cmd.Parameters.AddWithValue("@Start_Time", Start_Time.ToString("yyyy-MM-dd HH:mm:ss"));
        cmd.Parameters.AddWithValue("@End_Time", End_Time.ToString("yyyy-MM-dd HH:mm:ss"));
        cmd.Parameters.AddWithValue("@Drying_temp", Drying_temp);
        cmd.Parameters.AddWithValue("@Operator", Operator);
        cmd.Parameters.AddWithValue("@Remark", Remark);
        cmd.Parameters.AddWithValue("@UPDATED_BY", UPDATED_BY);
        cmd.Parameters.AddWithValue("@UPDATED_DATE", UPDATED_DATE);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }



    public int delete(int ID)
    {
        int i = 0;
        string sql = "Delete FROM siconide_baking WHERE ID=@ID";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@ID", ID);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }



}